/**
 * @filename MemberController.java
 */
package com.liupf.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.util.PathUtil;
import com.liupf.common.utils.Constants;
import com.liupf.common.utils.MD5Util;
import com.liupf.common.utils.TimeUtil;
import com.liupf.domain.Member;
import com.liupf.domain.Order;

/**
 * @description 会员管理
 * @author shevliu
 * @email shevliu@gmail.com Jul 28, 2012 3:20:49 PM
 */
public class MemberController extends Controller {

	private Log log = LogFactory.getLog(this.getClass());

	public void index() {
		
		//操作员自己所属品牌
		String company=(String) getSession().getAttribute("company");
		int purview=(Integer) getSession().getAttribute("purview");
		String userRole = (String)getSession().getAttribute("userRole");
		
		String realName = getPara("real_name");
		String cellPhone = getPara("cell_phone");
		StringBuffer from  = new StringBuffer();
		from.append("from  driver_member m where 1=1 ");
		if(StringUtils.isNotBlank(realName)){
			from.append(" and m.real_name='" + realName + "'") ;
		}
		if(StringUtils.isNotBlank(cellPhone)){
			from.append(" and m.cell_phone='" + cellPhone + "'") ;
		}
		
		/*if(Constants.ROLE_BRAND_MANAGER.equals(userRole) || Constants.ROLE_MIDDLE_MANAGER.equals(userRole)){
            from.append(" and m.company like '" + company + "%'");
        }*/
		
		from.append(" and m.user_level is null");
//会员本身 无品牌
		
		from.append(" order by id desc");
		Page<Member> page = Member.dao.paginate(getParaToInt(0, 1), 10,
				"select * , (select count(*) from order_detail d where d.member_id = m.id) order_count, (select sum(d.totalmoney) from order_detail d where d.member_id = m.id) sum_money ",
				from.toString());
		setAttr("page", page);
		render("member_index.ftl");
	}
	
	public void add(){
		render("member_add.ftl");
	}
	
	public void save(){
		Member member = getModel(Member.class);
		String password = member.getStr("password");
		member.put("password", MD5Util.MD5(password));
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = formatter.format(new Date());
		member.put("create_time", dateString);
		member.save();
		redirect("/member");
	}
	
	public void edit(){
		setAttr("member", Member.dao.findById(getParaToInt()));
		render("member_edit.ftl");
	}
	
	public void update(){
		Member member = getModel(Member.class);
		member.remove("password");
		member.update();
		redirect("/member");
	}
	
	public void delete(){
		int id = getParaToInt(0);
		Db.update("update driver_member set status=0 where id=?", id);
		redirect("/member");
	}
	
	public void black(){
		int id = getParaToInt(0);
		Db.update("update driver_member set status=2 where id=?", id);
		redirect("/member");
	}
	
	public void white(){
		int id = getParaToInt(0);
		Db.update("update driver_member set status=1 where id=?", id);
		redirect("/member");
	}
	/**
	 * 
	 * @description 根据手机号码查询会员 
	 * @author shevliu
	 * @email shevliu@gmail.com
	 * Jul 28, 2012 11:07:47 PM
	 */
	public void queryMemberByPhone(){
		String phone = getPara("cell_phone");
		Member member = Member.dao.findFirst("select *  ,(select count(*) from order_detail d where d.member_id = m.id) order_count, (select sum(d.charges) from order_detail d where d.member_id = m.id) sum_money from driver_member m where status=1 and cell_phone=?" , phone);
		if(member != null){
			int memberId = member.getInt("id");
			List<Order> orderList = Order.dao.find("select a.* ,c.name as driver_name , c.job_number,c.phone as driver_phone from order_detail a left join b_user c on a.b_user_id = c.id  where member_id=? or cell_phone = ? order by id desc" ,memberId , phone) ;
			setAttr("orderList", orderList);
		}
		setAttr("member", member);
		setAttr("cell_phone", phone);
		render("../order/new_order2.ftl");
	}
	
	public void exportXls() {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		HSSFRow row = sheet.createRow((short) 0);
		row.createCell(0).setCellValue("姓名");
		row.createCell(1).setCellValue("手机号码");
		row.createCell(2).setCellValue("性别");
		row.createCell(3).setCellValue("订单量");
		row.createCell(4).setCellValue("订单额");
		row.createCell(5).setCellValue("注册时间");
		row.createCell(6).setCellValue("状态");

		
		List<Record> memberList = Db.find("select * , (select count(*) from order_detail d where d.member_id = m.id) order_count, "
		                                  +"(select sum(d.totalmoney) from order_detail d where d.member_id = m.id) sum_money "
				                          +"from  driver_member m  order by id desc");
		
		for (int i = 0; i < memberList.size(); i++) {
			HSSFRow r = sheet.createRow(i + 1);
			Record record = memberList.get(i);
			
			r.createCell(0).setCellValue(record.getStr("real_name"));
			r.createCell(1).setCellValue(record.getStr("cell_phone"));
			int sex = record.getInt("sex");
			if(sex == 0) {
				r.createCell(2).setCellValue("男");
			} else if(sex == 1){
				r.createCell(2).setCellValue("女");
			} else {
				r.createCell(2).setCellValue("未知");
			}
			r.createCell(3).setCellValue(record.getLong("order_count"));
			r.createCell(4).setCellValue(record.getBigDecimal("sum_money") != null ? record.getBigDecimal("sum_money").doubleValue():0);
			r.createCell(5).setCellValue(record.getTimestamp("create_time") != null ? TimeUtil.format(record.getTimestamp("create_time"),"yyyy-MM-dd HH:mm:ss") : "");
			
			int status = record.getInt("status");
			if(status == 1) {
				r.createCell(6).setCellValue("正常");
			} else if(status ==2) {
				r.createCell(6).setCellValue("黑名单");
			} else {
				r.createCell(6).setCellValue("已删除");
			}
		}
		PathUtil.getWebRootPath();
		String fileName = PathUtil.getWebRootPath() + "/excel/member.xls";
		File f = new File(fileName);
		log.debug("file path:" + f.getAbsolutePath());
		FileOutputStream fOut;
		try {
			fOut = new FileOutputStream(fileName);
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
		} catch (Exception e) {
			throw new RuntimeException("导出excel失败，请稍后再试", e);
		}
		renderFile(f);
	}

	public void findByPhone()
	{

		String phone = getPara("phone");
		
		Member member = Member.dao.findFirst("select * from driver_member where status in (1,2) and cell_phone=?" , phone);
		
		int memberId = member.getInt("id");
		
		Record rec = Db	.findFirst(
			"select (select count(*) from order_detail where member_id =?) order_count," +
			" (select count(*) from order_detail  where member_id =?  and cancel is not null) cancel_order_count ," +
			" (select count(*) from order_detail where member_id =? and DATE_FORMAT(create_time, '%Y%m' )= DATE_FORMAT(CURDATE(), '%Y%m') ) month_order_count ," +
			" (select ifnull(sum(totalmoney),0) from order_detail where member_id =? and order_state=4 ) finishmoney;",
			memberId, memberId,memberId,memberId);
		setAttr("order_count",rec.getLong("order_count"));
		setAttr("cancel_order_count",rec.getLong("cancel_order_count"));
		setAttr("month_order_count",rec.getLong("month_order_count"));
		setAttr("finishmoney",rec.getBigDecimal("finishmoney"));
		
		setAttr("member", member);
		setAttr("cell_phone", phone);
		
	    renderJson();
		

	    
	   // List<User> userList = User.dao.find("select * from user");
	   // render("userList", userList);
	}
	
	public void toVIP(){
	    int id = getParaToInt(0);
        Db.update("update driver_member set user_level=2, user_type=0 where id=?", id);
        redirect("/member");
	}
	
	public void bussinessVIP(){
        int id = getParaToInt(0);
        Db.update("update driver_member set user_level=2, user_type=1 where id=?", id);
        redirect("/member");
    }
}
